﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    /// <summary>
    /// 
    /// </summary>
    public class E_ClassInfoDal : SqlServerHelper, IDisposable
    {
        public bool AddE_ClassInfo(E_ClassInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO E_ClassInfo");
            strSql.Append("(");
            strSql.Append("ClassName,");
            strSql.Append("UpStartTime,");
            strSql.Append("UpEndTime,");
            strSql.Append("DownStartTime,");
            strSql.Append("DownEndTime,");
            strSql.Append("Status");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@ClassName,");
            strSql.Append("@UpStartTime,");
            strSql.Append("@UpEndTime,");
            strSql.Append("@DownStartTime,");
            strSql.Append("@DownEndTime,");
            strSql.Append("@Status");
            strSql.Append(")");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@ClassName", SqlDbType.NVarChar, 100) { Value = model.ClassName },
                new SqlParameter("@UpStartTime", SqlDbType.Char, 10) { Value = model.UpStartTime },
                new SqlParameter("@UpEndTime", SqlDbType.Char, 10) { Value = model.UpEndTime },
                new SqlParameter("@DownStartTime", SqlDbType.Char, 10) { Value = model.DownStartTime },
                new SqlParameter("@DownEndTime", SqlDbType.Char, 10) { Value = model.DownEndTime },
                new SqlParameter("@Status", SqlDbType.TinyInt, 1) { Value = model.Status }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelE_ClassInfo(int Id)
        {
            string strSql = "UPDATE E_ClassInfo SET Status=1 WHERE Id=@Id";
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@Id", SqlDbType.Int, 4) { Value = Id }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool EditE_ClassInfo(E_ClassInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE E_ClassInfo SET ");
            strSql.Append(" ClassName=@ClassName,");
            strSql.Append(" UpStartTime=@UpStartTime,");
            strSql.Append(" UpEndTime=@UpEndTime,");
            strSql.Append(" DownStartTime=@DownStartTime,");
            strSql.Append(" DownEndTime=@DownEndTime,");
            strSql.Append(" Status=@Status");
            strSql.Append(" WHERE Id=@Id");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@Id", SqlDbType.Int, 4) { Value = model.Id },
                new SqlParameter("@ClassName", SqlDbType.NVarChar, 100) { Value = model.ClassName },
                new SqlParameter("@UpStartTime", SqlDbType.Char, 10) { Value = model.UpStartTime },
                new SqlParameter("@UpEndTime", SqlDbType.Char, 10) { Value = model.UpEndTime },
                new SqlParameter("@DownStartTime", SqlDbType.Char, 10) { Value = model.DownStartTime },
                new SqlParameter("@DownEndTime", SqlDbType.Char, 10) { Value = model.DownEndTime },
                new SqlParameter("@Status", SqlDbType.TinyInt, 1) { Value = model.Status }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public List<E_ClassInfo> GetE_ClassInfoList(string strWhere)
        {
            List<E_ClassInfo> list = new List<E_ClassInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" Id");
            strSql.Append(",ClassName");
            strSql.Append(",UpStartTime");
            strSql.Append(",UpEndTime");
            strSql.Append(",DownStartTime");
            strSql.Append(",DownEndTime");
            strSql.Append(",Status");
            strSql.AppendFormat(" FROM E_ClassInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int Id = r.GetOrdinal("Id");
                int ClassName = r.GetOrdinal("ClassName");
                int UpStartTime = r.GetOrdinal("UpStartTime");
                int UpEndTime = r.GetOrdinal("UpEndTime");
                int DownStartTime = r.GetOrdinal("DownStartTime");
                int DownEndTime = r.GetOrdinal("DownEndTime");
                int Status = r.GetOrdinal("Status");
                E_ClassInfo model = null;
                while (r.Read())
                {
                    model = new E_ClassInfo
                    {
                        Id = r.GetInt32(Id),
                        ClassName = r.GetString(ClassName),
                        UpStartTime = r.GetString(UpStartTime),
                        UpEndTime = r.GetString(UpEndTime),
                        DownStartTime = r.GetString(DownStartTime),
                        DownEndTime = r.GetString(DownEndTime),
                        Status = r.GetByte(Status)
                    };
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public List<E_ClassInfo> GetE_ClassInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<E_ClassInfo> list = new List<E_ClassInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS ORDERBYID");
            strSql.Append(",Id");
            strSql.Append(",ClassName");
            strSql.Append(",UpStartTime");
            strSql.Append(",UpEndTime");
            strSql.Append(",DownStartTime");
            strSql.Append(",DownEndTime");
            strSql.Append(",Status");
            strSql.AppendFormat(" FROM E_ClassInfo WITH(NOLOCK) WHERE 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int Id = r.GetOrdinal("Id");
                int ClassName = r.GetOrdinal("ClassName");
                int UpStartTime = r.GetOrdinal("UpStartTime");
                int UpEndTime = r.GetOrdinal("UpEndTime");
                int DownStartTime = r.GetOrdinal("DownStartTime");
                int DownEndTime = r.GetOrdinal("DownEndTime");
                int Status = r.GetOrdinal("Status");
                E_ClassInfo model = null;
                while (r.Read())
                {
                    model = new E_ClassInfo
                    {
                        Id = r.GetInt32(Id),
                        ClassName = r.GetString(ClassName),
                        UpStartTime = r.GetString(UpStartTime),
                        UpEndTime = r.GetString(UpEndTime),
                        DownStartTime = r.GetString(DownStartTime),
                        DownEndTime = r.GetString(DownEndTime),
                        Status = r.GetByte(Status)
                    };
                    list.Add(model);
                }
                r.Close();
            }
            totalCount = GetE_ClassInfoCount(strWhere);
            return list;
        }

        public int GetE_ClassInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM E_ClassInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        public E_ClassInfo GetE_ClassInfoModel(int id)
        {
            E_ClassInfo model = new E_ClassInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("Id");
            strSql.Append(",ClassName");
            strSql.Append(",UpStartTime");
            strSql.Append(",UpEndTime");
            strSql.Append(",DownStartTime");
            strSql.Append(",DownEndTime");
            strSql.Append(",Status");
            strSql.Append(" FROM E_ClassInfo WITH(NOLOCK) WHERE Id=@Id");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@Id", SqlDbType.Int, 4) { Value = id }
            };
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int Id = r.GetOrdinal("Id");
                int ClassName = r.GetOrdinal("ClassName");
                int UpStartTime = r.GetOrdinal("UpStartTime");
                int UpEndTime = r.GetOrdinal("UpEndTime");
                int DownStartTime = r.GetOrdinal("DownStartTime");
                int DownEndTime = r.GetOrdinal("DownEndTime");
                int Status = r.GetOrdinal("Status");
                if (r.Read())
                {
                    model.Id = r.GetInt32(Id);
                    model.ClassName = r.GetString(ClassName);
                    model.UpStartTime = r.GetString(UpStartTime);
                    model.UpEndTime = r.GetString(UpEndTime);
                    model.DownStartTime = r.GetString(DownStartTime);
                    model.DownEndTime = r.GetString(DownEndTime);
                    model.Status = r.GetByte(Status);
                }
                r.Close();
            }
            return model;
        }
    }
}
